{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-03-29T02:37:14.085125Z",
     "start_time": "2024-03-29T02:37:14.081040Z"
    }
   },
   "outputs": [],
   "source": [
    "import pymysql\n",
    "import requests\n",
    "from tqdm import tqdm\n",
    "from urllib.parse import urlparse\n",
    "import sys\n",
    "from sqlalchemy import create_engine\n",
    "from datetime import datetime\n",
    "import pandas as pd\n",
    "\n",
    "\n",
    "def mysql_engine(host, port, user, passwd, db=None):\n",
    "    try:\n",
    "        engine = create_engine(f'mysql+pymysql://{user}:{passwd}@{host}:{port}/{db}')\n",
    "        return engine\n",
    "    except Exception as e:\n",
    "        print(f\"An error occurred: {e}\")\n",
    "\n",
    "\n",
    "mysql_rule = mysql_engine('172.20.1.186',3306,'longxiaolei','WWsCQ9TXG+BJBHlK','rule')\n",
    "# mysql_rule = mysql_engine('172.20.1.129', 3306, 'rule', 'Z3Vl7enFj2eqkeJf', 'rule')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-03-29T02:38:08.411854Z",
     "start_time": "2024-03-29T02:37:56.843077Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "近24小时的数据情况：(15220, 8)\n"
     ]
    }
   ],
   "source": [
    "monitor_hour = 24\n",
    "sql = f\"\"\"\n",
    "SELECT a.merchant_id,\n",
    "   a.tx_id,\n",
    "   a.create_time,\n",
    "   a.req_data,\n",
    "   a.type type_urule,\n",
    "   b.type,\n",
    "   b.status,\n",
    "   b.apply_info\n",
    "from risk.t_risk_urule_req_log a\n",
    "left join risk.t_risk_req_record b on a.tx_id = b.tx_id and a.merchant_id = b.merchant_id\n",
    "where\n",
    "a.create_time >=  DATE_SUB(NOW(), INTERVAL {monitor_hour} HOUR)\n",
    "and a.merchant_id in ('AM','AC','AL','AG')\n",
    "order by a.create_time desc\n",
    "\"\"\"\n",
    "urule_log_df = pd.read_sql(sql, mysql_rule)\n",
    "print(f\"近{monitor_hour}小时的数据情况：{urule_log_df.shape}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-03-29T02:39:08.258895Z",
     "start_time": "2024-03-29T02:38:55.088786Z"
    }
   },
   "outputs": [],
   "source": [
    "def search_json(json_str, query):\n",
    "    import json\n",
    "    obj = json.loads(json_str)\n",
    "    return query_dict(obj, query)\n",
    "\n",
    "\n",
    "def query_dict(data, query):\n",
    "    keys = query.strip(\"$.\").split(\".\")\n",
    "    result = data\n",
    "    for key in keys:\n",
    "        if isinstance(result, dict) and key in result:\n",
    "            result = result[key]\n",
    "        else:\n",
    "            return None\n",
    "    return result\n",
    "\n",
    "\n",
    "def query_json(json_str, querys):\n",
    "    import json\n",
    "    if pd.isna(json_str):\n",
    "        return None\n",
    "    try:\n",
    "        obj = json.loads(json_str)\n",
    "        if isinstance(querys, str) and len(querys) > 0:\n",
    "            return query_dict(obj, querys)\n",
    "        if isinstance(querys, list) and len(querys) > 0:\n",
    "            result_arr = []\n",
    "            for query in querys:\n",
    "                result_arr.append(query_dict(obj, query))\n",
    "            return tuple(result_arr)\n",
    "    except json.JSONDecodeError:\n",
    "        print(f\"发生异常json: \\n{json_str}\")\n",
    "\n",
    "\n",
    "columns = ['userInfo.userRecord.phoneNumber', 'userInfo.userRecord.appUserId', 'userInfo.userRecord.idCardNumber']\n",
    "urule_log_df[columns] = urule_log_df.apply(lambda x: query_json(x['apply_info'], columns), axis=1, result_type='expand')\n",
    "\n",
    "columns = ['clientType', 'acqChannel', 'userType', 'endBillsCnt',\n",
    "           'modelScoreParams_scoreV1', 'modelScoreParams_scoreV2', 'modelScoreParams_scoreV3','modelScoreParams_scoreV4', 'modelScoreParams_scoreV5','modelScoreParams_scoreV6', 'modelScoreParams_scoreV7', 'modelScoreParams_scoreV8','modelScoreParams_scoreV9', 'modelScoreParams_scoreV10','modelScoreParams_scoreV11','modelScoreParams_scoreV12','modelScoreParams_scoreV13']\n",
    "urule_log_df[columns] = urule_log_df.apply(lambda x: query_json(x['req_data'], columns), axis=1, result_type='expand')\n",
    "\n",
    "urule_log_df = urule_log_df.rename(columns={\n",
    "    'modelScoreParams_scoreV1': 'scorev1',\n",
    "    'modelScoreParams_scoreV2': 'scorev2',\n",
    "    'modelScoreParams_scoreV3': 'scorev3',\n",
    "    'modelScoreParams_scoreV4': 'scorev4',\n",
    "    'modelScoreParams_scoreV5': 'scorev5',\n",
    "    'modelScoreParams_scoreV6': 'scorev6',\n",
    "    'modelScoreParams_scoreV7': 'scorev7',\n",
    "    'modelScoreParams_scoreV8': 'scorev8',\n",
    "    'modelScoreParams_scoreV9': 'scorev9',\n",
    "    'modelScoreParams_scoreV10': 'scorev10',\n",
    "    'modelScoreParams_scoreV11': 'scorev11',\n",
    "    'modelScoreParams_scoreV12': 'scorev12',\n",
    "    'modelScoreParams_scoreV13': 'scorev13',\n",
    "    'userInfo.userRecord.phoneNumber': 'phone',\n",
    "    'userInfo.userRecord.appUserId': 'user_id',\n",
    "    'userInfo.userRecord.idCardNumber': 'idcard_number',\n",
    "})\n",
    "score_cols = ['scorev1', 'scorev2', 'scorev3', 'scorev4', 'scorev5', 'scorev6', 'scorev7', 'scorev8', 'scorev9',\n",
    "              'scorev10','scorev11','scorev12','scorev13']\n",
    "urule_log_df[score_cols] = urule_log_df[score_cols].astype(float)\n",
    "\n",
    "urule_log_df['clientType1'] = urule_log_df['clientType'].map(lambda x: 'ios' if x == 1 else 'android')\n",
    "urule_log_df['type1'] = urule_log_df['type'].map(lambda x: '进件' if x == 1 else ('授信' if x == 2 else '其他'))\n",
    "urule_log_df['endBillsCnt1'] = urule_log_df['endBillsCnt'].map(\n",
    "    lambda x: '新客' if float(x) <= 0 else ('老客' if float(x) > 0 else '其他'))\n",
    "\n",
    "urule_log_df_ios = urule_log_df[urule_log_df.clientType1 == 'ios']\n",
    "urule_log_df = urule_log_df[urule_log_df.clientType1 == 'android']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "outputs": [
    {
     "data": {
      "text/plain": "  merchant_id                tx_id         create_time  \\\n0          AC  1223098675116929024 2024-03-29 10:37:31   \n1          AG  1223098660259336192 2024-03-29 10:37:26   \n4          AC  1223098561006694400 2024-03-29 10:37:08   \n5          AG  1223098560690753536 2024-03-29 10:37:04   \n8          AC  1223098310334115840 2024-03-29 10:36:13   \n\n                                            req_data  type_urule  type  \\\n0  {\"ADVANCE_GD_X_1\": -99, \"ADVANCE_GD_X_10\": -99...           2     2   \n1  {\"ADVANCE_GD_X_1\": -99, \"ADVANCE_GD_X_10\": -99...           2     2   \n4  {\"ADVANCE_GD_X_1\": -99, \"ADVANCE_GD_X_10\": -99...           1     1   \n5  {\"ADVANCE_GD_X_1\": -99, \"ADVANCE_GD_X_10\": -99...           1     1   \n8  {\"ADVANCE_GD_X_1\": -99, \"ADVANCE_GD_X_10\": -99...           1     1   \n\n   status                                         apply_info       phone  \\\n0       0  {\"userBankInfo\":{\"appUserId\":\"1214669444347359...   922378921   \n1       0  {\"userBankInfo\":{\"appUserId\":\"1223098647735144...  3197794960   \n4       1  {\"productInfo\":{\"amount\":20000.00,\"productCode...   983452797   \n5       1  {\"productInfo\":{\"amount\":150000.00,\"productCod...  3102876379   \n8       1  {\"productInfo\":{\"amount\":20000.00,\"productCode...   997732094   \n\n               user_id  ... scorev7  scorev8 scorev9  scorev10 scorev11  \\\n0  1214669444347359232  ...  -999.0   -999.0  -999.0    -999.0    590.0   \n1  1223098647735144448  ...  -999.0   -999.0  -999.0    -999.0   -999.0   \n4  1195001408090828800  ...  -999.0   -999.0  -999.0    -999.0    618.0   \n5  1223098283652780032  ...  -999.0   -999.0  -999.0    -999.0   -999.0   \n8  1195558711298912256  ...  -999.0   -999.0  -999.0    -999.0    606.0   \n\n   scorev12  scorev13  clientType1  type1  endBillsCnt1  \n0     513.0    -999.0      android     授信            新客  \n1    -999.0     540.0      android     授信            新客  \n4     535.0    -999.0      android     进件            新客  \n5    -999.0     504.0      android     进件            新客  \n8     515.0    -999.0      android     进件            新客  \n\n[5 rows x 31 columns]",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>merchant_id</th>\n      <th>tx_id</th>\n      <th>create_time</th>\n      <th>req_data</th>\n      <th>type_urule</th>\n      <th>type</th>\n      <th>status</th>\n      <th>apply_info</th>\n      <th>phone</th>\n      <th>user_id</th>\n      <th>...</th>\n      <th>scorev7</th>\n      <th>scorev8</th>\n      <th>scorev9</th>\n      <th>scorev10</th>\n      <th>scorev11</th>\n      <th>scorev12</th>\n      <th>scorev13</th>\n      <th>clientType1</th>\n      <th>type1</th>\n      <th>endBillsCnt1</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>AC</td>\n      <td>1223098675116929024</td>\n      <td>2024-03-29 10:37:31</td>\n      <td>{\"ADVANCE_GD_X_1\": -99, \"ADVANCE_GD_X_10\": -99...</td>\n      <td>2</td>\n      <td>2</td>\n      <td>0</td>\n      <td>{\"userBankInfo\":{\"appUserId\":\"1214669444347359...</td>\n      <td>922378921</td>\n      <td>1214669444347359232</td>\n      <td>...</td>\n      <td>-999.0</td>\n      <td>-999.0</td>\n      <td>-999.0</td>\n      <td>-999.0</td>\n      <td>590.0</td>\n      <td>513.0</td>\n      <td>-999.0</td>\n      <td>android</td>\n      <td>授信</td>\n      <td>新客</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>AG</td>\n      <td>1223098660259336192</td>\n      <td>2024-03-29 10:37:26</td>\n      <td>{\"ADVANCE_GD_X_1\": -99, \"ADVANCE_GD_X_10\": -99...</td>\n      <td>2</td>\n      <td>2</td>\n      <td>0</td>\n      <td>{\"userBankInfo\":{\"appUserId\":\"1223098647735144...</td>\n      <td>3197794960</td>\n      <td>1223098647735144448</td>\n      <td>...</td>\n      <td>-999.0</td>\n      <td>-999.0</td>\n      <td>-999.0</td>\n      <td>-999.0</td>\n      <td>-999.0</td>\n      <td>-999.0</td>\n      <td>540.0</td>\n      <td>android</td>\n      <td>授信</td>\n      <td>新客</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>AC</td>\n      <td>1223098561006694400</td>\n      <td>2024-03-29 10:37:08</td>\n      <td>{\"ADVANCE_GD_X_1\": -99, \"ADVANCE_GD_X_10\": -99...</td>\n      <td>1</td>\n      <td>1</td>\n      <td>1</td>\n      <td>{\"productInfo\":{\"amount\":20000.00,\"productCode...</td>\n      <td>983452797</td>\n      <td>1195001408090828800</td>\n      <td>...</td>\n      <td>-999.0</td>\n      <td>-999.0</td>\n      <td>-999.0</td>\n      <td>-999.0</td>\n      <td>618.0</td>\n      <td>535.0</td>\n      <td>-999.0</td>\n      <td>android</td>\n      <td>进件</td>\n      <td>新客</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>AG</td>\n      <td>1223098560690753536</td>\n      <td>2024-03-29 10:37:04</td>\n      <td>{\"ADVANCE_GD_X_1\": -99, \"ADVANCE_GD_X_10\": -99...</td>\n      <td>1</td>\n      <td>1</td>\n      <td>1</td>\n      <td>{\"productInfo\":{\"amount\":150000.00,\"productCod...</td>\n      <td>3102876379</td>\n      <td>1223098283652780032</td>\n      <td>...</td>\n      <td>-999.0</td>\n      <td>-999.0</td>\n      <td>-999.0</td>\n      <td>-999.0</td>\n      <td>-999.0</td>\n      <td>-999.0</td>\n      <td>504.0</td>\n      <td>android</td>\n      <td>进件</td>\n      <td>新客</td>\n    </tr>\n    <tr>\n      <th>8</th>\n      <td>AC</td>\n      <td>1223098310334115840</td>\n      <td>2024-03-29 10:36:13</td>\n      <td>{\"ADVANCE_GD_X_1\": -99, \"ADVANCE_GD_X_10\": -99...</td>\n      <td>1</td>\n      <td>1</td>\n      <td>1</td>\n      <td>{\"productInfo\":{\"amount\":20000.00,\"productCode...</td>\n      <td>997732094</td>\n      <td>1195558711298912256</td>\n      <td>...</td>\n      <td>-999.0</td>\n      <td>-999.0</td>\n      <td>-999.0</td>\n      <td>-999.0</td>\n      <td>606.0</td>\n      <td>515.0</td>\n      <td>-999.0</td>\n      <td>android</td>\n      <td>进件</td>\n      <td>新客</td>\n    </tr>\n  </tbody>\n</table>\n<p>5 rows × 31 columns</p>\n</div>"
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "urule_log_df.head()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-03-29T02:39:11.205271Z",
     "start_time": "2024-03-29T02:39:11.187488Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-03-29T02:39:28.355659Z",
     "start_time": "2024-03-29T02:39:28.313591Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "安卓近24小时urule日志中评分的缺失情况为:\n"
     ]
    },
    {
     "data": {
      "text/plain": "   type1 endBillsCnt1 merchant_id acqChannel  order_c  missing_c  missing_r\n0     授信           新客          AC       AUMA      9.0        5.0   0.555556\n1     授信           新客          AC       SCSH    645.0      322.0   0.499225\n2     授信           新客          AC       SUTO    101.0       36.0   0.356436\n3     授信           新客          AC       TFOS    626.0      393.0   0.627796\n4     授信           新客          AG       CPDO    812.0      560.0   0.689655\n5     授信           新客          AG       SELN    191.0       80.0   0.418848\n6     授信           新客          AL       AMDO    288.0      233.0   0.809028\n7     授信           新客          AM       ADNF   4075.0     3513.0   0.862086\n8     授信           新客          AM       AFNC    718.0      574.0   0.799443\n9     授信           新客          AM       ASDI    161.0      108.0   0.670807\n10    授信           老客          AC       AUMA     58.0       19.0   0.327586\n11    授信           老客          AC       SCSH    513.0      175.0   0.341131\n12    授信           老客          AC       SUTO    115.0       63.0   0.547826\n13    授信           老客          AC       TFOS    276.0      146.0   0.528986\n14    授信           老客          AG       CPDO    209.0       96.0   0.459330\n15    授信           老客          AG       SELN    168.0       47.0   0.279762\n16    授信           老客          AL       AMDO    138.0       85.0   0.615942\n17    授信           老客          AM       ADNF    609.0      268.0   0.440066\n18    授信           老客          AM       AFNC     77.0       64.0   0.831169\n19    授信           老客          AM       ASDI    216.0       56.0   0.259259\n20    进件           新客          AC       SCSH    143.0       91.0   0.636364\n21    进件           新客          AC       SUTO     42.0       18.0   0.428571\n22    进件           新客          AC       TFOS    254.0      178.0   0.700787\n23    进件           新客          AG       CPDO    473.0      402.0   0.849894\n24    进件           新客          AG       SELN     56.0       42.0   0.750000\n25    进件           新客          AL       AMDO    176.0      149.0   0.846591\n26    进件           新客          AM       ADNF     73.0        6.0   0.082192\n27    进件           新客          AM       AFNC     16.0        1.0   0.062500\n28    进件           新客          AM       ASDI      7.0        0.0   0.000000\n29    进件           老客          AC       AUMA      7.0        3.0   0.428571\n30    进件           老客          AC       SCSH    144.0       45.0   0.312500\n31    进件           老客          AC       SUTO     16.0        3.0   0.187500\n32    进件           老客          AC       TFOS    114.0       47.0   0.412281\n33    进件           老客          AG       CPDO    124.0       86.0   0.693548\n34    进件           老客          AG       SELN     66.0       25.0   0.378788\n35    进件           老客          AL       AMDO     95.0       49.0   0.515789\n36    进件           老客          AM       ADNF     45.0        1.0   0.022222\n37    进件           老客          AM       ASDI     14.0        0.0   0.000000",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>type1</th>\n      <th>endBillsCnt1</th>\n      <th>merchant_id</th>\n      <th>acqChannel</th>\n      <th>order_c</th>\n      <th>missing_c</th>\n      <th>missing_r</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>授信</td>\n      <td>新客</td>\n      <td>AC</td>\n      <td>AUMA</td>\n      <td>9.0</td>\n      <td>5.0</td>\n      <td>0.555556</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>授信</td>\n      <td>新客</td>\n      <td>AC</td>\n      <td>SCSH</td>\n      <td>645.0</td>\n      <td>322.0</td>\n      <td>0.499225</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>授信</td>\n      <td>新客</td>\n      <td>AC</td>\n      <td>SUTO</td>\n      <td>101.0</td>\n      <td>36.0</td>\n      <td>0.356436</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>授信</td>\n      <td>新客</td>\n      <td>AC</td>\n      <td>TFOS</td>\n      <td>626.0</td>\n      <td>393.0</td>\n      <td>0.627796</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>授信</td>\n      <td>新客</td>\n      <td>AG</td>\n      <td>CPDO</td>\n      <td>812.0</td>\n      <td>560.0</td>\n      <td>0.689655</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>授信</td>\n      <td>新客</td>\n      <td>AG</td>\n      <td>SELN</td>\n      <td>191.0</td>\n      <td>80.0</td>\n      <td>0.418848</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>授信</td>\n      <td>新客</td>\n      <td>AL</td>\n      <td>AMDO</td>\n      <td>288.0</td>\n      <td>233.0</td>\n      <td>0.809028</td>\n    </tr>\n    <tr>\n      <th>7</th>\n      <td>授信</td>\n      <td>新客</td>\n      <td>AM</td>\n      <td>ADNF</td>\n      <td>4075.0</td>\n      <td>3513.0</td>\n      <td>0.862086</td>\n    </tr>\n    <tr>\n      <th>8</th>\n      <td>授信</td>\n      <td>新客</td>\n      <td>AM</td>\n      <td>AFNC</td>\n      <td>718.0</td>\n      <td>574.0</td>\n      <td>0.799443</td>\n    </tr>\n    <tr>\n      <th>9</th>\n      <td>授信</td>\n      <td>新客</td>\n      <td>AM</td>\n      <td>ASDI</td>\n      <td>161.0</td>\n      <td>108.0</td>\n      <td>0.670807</td>\n    </tr>\n    <tr>\n      <th>10</th>\n      <td>授信</td>\n      <td>老客</td>\n      <td>AC</td>\n      <td>AUMA</td>\n      <td>58.0</td>\n      <td>19.0</td>\n      <td>0.327586</td>\n    </tr>\n    <tr>\n      <th>11</th>\n      <td>授信</td>\n      <td>老客</td>\n      <td>AC</td>\n      <td>SCSH</td>\n      <td>513.0</td>\n      <td>175.0</td>\n      <td>0.341131</td>\n    </tr>\n    <tr>\n      <th>12</th>\n      <td>授信</td>\n      <td>老客</td>\n      <td>AC</td>\n      <td>SUTO</td>\n      <td>115.0</td>\n      <td>63.0</td>\n      <td>0.547826</td>\n    </tr>\n    <tr>\n      <th>13</th>\n      <td>授信</td>\n      <td>老客</td>\n      <td>AC</td>\n      <td>TFOS</td>\n      <td>276.0</td>\n      <td>146.0</td>\n      <td>0.528986</td>\n    </tr>\n    <tr>\n      <th>14</th>\n      <td>授信</td>\n      <td>老客</td>\n      <td>AG</td>\n      <td>CPDO</td>\n      <td>209.0</td>\n      <td>96.0</td>\n      <td>0.459330</td>\n    </tr>\n    <tr>\n      <th>15</th>\n      <td>授信</td>\n      <td>老客</td>\n      <td>AG</td>\n      <td>SELN</td>\n      <td>168.0</td>\n      <td>47.0</td>\n      <td>0.279762</td>\n    </tr>\n    <tr>\n      <th>16</th>\n      <td>授信</td>\n      <td>老客</td>\n      <td>AL</td>\n      <td>AMDO</td>\n      <td>138.0</td>\n      <td>85.0</td>\n      <td>0.615942</td>\n    </tr>\n    <tr>\n      <th>17</th>\n      <td>授信</td>\n      <td>老客</td>\n      <td>AM</td>\n      <td>ADNF</td>\n      <td>609.0</td>\n      <td>268.0</td>\n      <td>0.440066</td>\n    </tr>\n    <tr>\n      <th>18</th>\n      <td>授信</td>\n      <td>老客</td>\n      <td>AM</td>\n      <td>AFNC</td>\n      <td>77.0</td>\n      <td>64.0</td>\n      <td>0.831169</td>\n    </tr>\n    <tr>\n      <th>19</th>\n      <td>授信</td>\n      <td>老客</td>\n      <td>AM</td>\n      <td>ASDI</td>\n      <td>216.0</td>\n      <td>56.0</td>\n      <td>0.259259</td>\n    </tr>\n    <tr>\n      <th>20</th>\n      <td>进件</td>\n      <td>新客</td>\n      <td>AC</td>\n      <td>SCSH</td>\n      <td>143.0</td>\n      <td>91.0</td>\n      <td>0.636364</td>\n    </tr>\n    <tr>\n      <th>21</th>\n      <td>进件</td>\n      <td>新客</td>\n      <td>AC</td>\n      <td>SUTO</td>\n      <td>42.0</td>\n      <td>18.0</td>\n      <td>0.428571</td>\n    </tr>\n    <tr>\n      <th>22</th>\n      <td>进件</td>\n      <td>新客</td>\n      <td>AC</td>\n      <td>TFOS</td>\n      <td>254.0</td>\n      <td>178.0</td>\n      <td>0.700787</td>\n    </tr>\n    <tr>\n      <th>23</th>\n      <td>进件</td>\n      <td>新客</td>\n      <td>AG</td>\n      <td>CPDO</td>\n      <td>473.0</td>\n      <td>402.0</td>\n      <td>0.849894</td>\n    </tr>\n    <tr>\n      <th>24</th>\n      <td>进件</td>\n      <td>新客</td>\n      <td>AG</td>\n      <td>SELN</td>\n      <td>56.0</td>\n      <td>42.0</td>\n      <td>0.750000</td>\n    </tr>\n    <tr>\n      <th>25</th>\n      <td>进件</td>\n      <td>新客</td>\n      <td>AL</td>\n      <td>AMDO</td>\n      <td>176.0</td>\n      <td>149.0</td>\n      <td>0.846591</td>\n    </tr>\n    <tr>\n      <th>26</th>\n      <td>进件</td>\n      <td>新客</td>\n      <td>AM</td>\n      <td>ADNF</td>\n      <td>73.0</td>\n      <td>6.0</td>\n      <td>0.082192</td>\n    </tr>\n    <tr>\n      <th>27</th>\n      <td>进件</td>\n      <td>新客</td>\n      <td>AM</td>\n      <td>AFNC</td>\n      <td>16.0</td>\n      <td>1.0</td>\n      <td>0.062500</td>\n    </tr>\n    <tr>\n      <th>28</th>\n      <td>进件</td>\n      <td>新客</td>\n      <td>AM</td>\n      <td>ASDI</td>\n      <td>7.0</td>\n      <td>0.0</td>\n      <td>0.000000</td>\n    </tr>\n    <tr>\n      <th>29</th>\n      <td>进件</td>\n      <td>老客</td>\n      <td>AC</td>\n      <td>AUMA</td>\n      <td>7.0</td>\n      <td>3.0</td>\n      <td>0.428571</td>\n    </tr>\n    <tr>\n      <th>30</th>\n      <td>进件</td>\n      <td>老客</td>\n      <td>AC</td>\n      <td>SCSH</td>\n      <td>144.0</td>\n      <td>45.0</td>\n      <td>0.312500</td>\n    </tr>\n    <tr>\n      <th>31</th>\n      <td>进件</td>\n      <td>老客</td>\n      <td>AC</td>\n      <td>SUTO</td>\n      <td>16.0</td>\n      <td>3.0</td>\n      <td>0.187500</td>\n    </tr>\n    <tr>\n      <th>32</th>\n      <td>进件</td>\n      <td>老客</td>\n      <td>AC</td>\n      <td>TFOS</td>\n      <td>114.0</td>\n      <td>47.0</td>\n      <td>0.412281</td>\n    </tr>\n    <tr>\n      <th>33</th>\n      <td>进件</td>\n      <td>老客</td>\n      <td>AG</td>\n      <td>CPDO</td>\n      <td>124.0</td>\n      <td>86.0</td>\n      <td>0.693548</td>\n    </tr>\n    <tr>\n      <th>34</th>\n      <td>进件</td>\n      <td>老客</td>\n      <td>AG</td>\n      <td>SELN</td>\n      <td>66.0</td>\n      <td>25.0</td>\n      <td>0.378788</td>\n    </tr>\n    <tr>\n      <th>35</th>\n      <td>进件</td>\n      <td>老客</td>\n      <td>AL</td>\n      <td>AMDO</td>\n      <td>95.0</td>\n      <td>49.0</td>\n      <td>0.515789</td>\n    </tr>\n    <tr>\n      <th>36</th>\n      <td>进件</td>\n      <td>老客</td>\n      <td>AM</td>\n      <td>ADNF</td>\n      <td>45.0</td>\n      <td>1.0</td>\n      <td>0.022222</td>\n    </tr>\n    <tr>\n      <th>37</th>\n      <td>进件</td>\n      <td>老客</td>\n      <td>AM</td>\n      <td>ASDI</td>\n      <td>14.0</td>\n      <td>0.0</td>\n      <td>0.000000</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "missing_cond = (\n",
    "        (\n",
    "                (urule_log_df[\"merchant_id\"] == \"AC\") & ((urule_log_df[\"scorev2\"] < 0) | (urule_log_df[\"scorev3\"] < 0) |\n",
    "                                                         (urule_log_df[\"scorev11\"] < 0) | (urule_log_df[\"scorev12\"] < 0)\n",
    "                                                         )\n",
    "        )\n",
    "        | (\n",
    "                (urule_log_df[\"merchant_id\"] == \"AM\")\n",
    "                & (\n",
    "                        (urule_log_df[\"scorev1\"] < 0)\n",
    "                        | (urule_log_df[\"scorev4\"] < 0)\n",
    "                        | (urule_log_df[\"scorev5\"] < 0)\n",
    "                        | (urule_log_df[\"scorev6\"] < 0)\n",
    "                        | (urule_log_df[\"scorev8\"] < 0)\n",
    "                        | (urule_log_df[\"scorev9\"] < 0)\n",
    "                        | (urule_log_df[\"scorev10\"] < 0)\n",
    "                )\n",
    "        )\n",
    "        | ((urule_log_df[\"merchant_id\"] == \"AL\") & (urule_log_df[\"scorev7\"] < 0))\n",
    "        | ((urule_log_df[\"merchant_id\"] == \"AG\") & (urule_log_df[\"scorev13\"] < 0))\n",
    ")\n",
    "\n",
    "missing_order_count = urule_log_df[missing_cond].groupby(['type1', 'endBillsCnt1', 'merchant_id', 'acqChannel'])[\n",
    "    'tx_id'].count()\n",
    "order_count = urule_log_df.groupby(['type1', 'endBillsCnt1', 'merchant_id', 'acqChannel'])['tx_id'].count()\n",
    "result = pd.concat([order_count, missing_order_count, missing_order_count / order_count], axis=1)\n",
    "result.columns = ['order_c', 'missing_c', 'missing_r']\n",
    "result['order_c'] = result['order_c'].astype(float)\n",
    "result = result.fillna(0)\n",
    "result = result.reset_index()\n",
    "# md_text = f\"近{monitor_hour}小时urule日志中评分的缺失情况为:\\n {result.to_string()}\"\n",
    "# print(md_text)\n",
    "print(f\"安卓近{monitor_hour}小时urule日志中评分的缺失情况为:\")\n",
    "result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-03-29T02:39:40.541092Z",
     "start_time": "2024-03-29T02:39:40.515544Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "ios近24小时urule日志中评分的缺失情况为:\n"
     ]
    },
    {
     "data": {
      "text/plain": "  type1 endBillsCnt1 merchant_id acqChannel  order_c  missing_c  missing_r\n0    授信           新客          AM       AIMX    605.0      248.0   0.409917\n1    授信           新客          AM       ATCC    332.0      131.0   0.394578\n2    授信           老客          AM       AIMX   1365.0      362.0   0.265201\n3    授信           老客          AM       ATCC    360.0      117.0   0.325000\n4    进件           新客          AL       AODO     13.0        0.0   0.000000\n5    进件           新客          AM       AIMX    278.0      172.0   0.618705\n6    进件           新客          AM       ATCC     77.0       40.0   0.519481\n7    进件           老客          AL       AODO     13.0        0.0   0.000000\n8    进件           老客          AM       AIMX    246.0       34.0   0.138211\n9    进件           老客          AM       ATCC     61.0        7.0   0.114754",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>type1</th>\n      <th>endBillsCnt1</th>\n      <th>merchant_id</th>\n      <th>acqChannel</th>\n      <th>order_c</th>\n      <th>missing_c</th>\n      <th>missing_r</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>授信</td>\n      <td>新客</td>\n      <td>AM</td>\n      <td>AIMX</td>\n      <td>605.0</td>\n      <td>248.0</td>\n      <td>0.409917</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>授信</td>\n      <td>新客</td>\n      <td>AM</td>\n      <td>ATCC</td>\n      <td>332.0</td>\n      <td>131.0</td>\n      <td>0.394578</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>授信</td>\n      <td>老客</td>\n      <td>AM</td>\n      <td>AIMX</td>\n      <td>1365.0</td>\n      <td>362.0</td>\n      <td>0.265201</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>授信</td>\n      <td>老客</td>\n      <td>AM</td>\n      <td>ATCC</td>\n      <td>360.0</td>\n      <td>117.0</td>\n      <td>0.325000</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>进件</td>\n      <td>新客</td>\n      <td>AL</td>\n      <td>AODO</td>\n      <td>13.0</td>\n      <td>0.0</td>\n      <td>0.000000</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>进件</td>\n      <td>新客</td>\n      <td>AM</td>\n      <td>AIMX</td>\n      <td>278.0</td>\n      <td>172.0</td>\n      <td>0.618705</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>进件</td>\n      <td>新客</td>\n      <td>AM</td>\n      <td>ATCC</td>\n      <td>77.0</td>\n      <td>40.0</td>\n      <td>0.519481</td>\n    </tr>\n    <tr>\n      <th>7</th>\n      <td>进件</td>\n      <td>老客</td>\n      <td>AL</td>\n      <td>AODO</td>\n      <td>13.0</td>\n      <td>0.0</td>\n      <td>0.000000</td>\n    </tr>\n    <tr>\n      <th>8</th>\n      <td>进件</td>\n      <td>老客</td>\n      <td>AM</td>\n      <td>AIMX</td>\n      <td>246.0</td>\n      <td>34.0</td>\n      <td>0.138211</td>\n    </tr>\n    <tr>\n      <th>9</th>\n      <td>进件</td>\n      <td>老客</td>\n      <td>AM</td>\n      <td>ATCC</td>\n      <td>61.0</td>\n      <td>7.0</td>\n      <td>0.114754</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "missing_cond = (\n",
    "    (urule_log_df_ios['merchant_id']=='AM') & (urule_log_df_ios['scorev5']<0 )\n",
    "                )\n",
    "missing_order_count = urule_log_df_ios[missing_cond].groupby(['type1', 'endBillsCnt1', 'merchant_id', 'acqChannel'])[\n",
    "    'tx_id'].count()\n",
    "order_count = urule_log_df_ios.groupby(['type1', 'endBillsCnt1', 'merchant_id', 'acqChannel'])['tx_id'].count()\n",
    "result = pd.concat([order_count, missing_order_count, missing_order_count / order_count], axis=1)\n",
    "result.columns = ['order_c', 'missing_c', 'missing_r']\n",
    "result['order_c'] = result['order_c'].astype(float)\n",
    "result = result.fillna(0)\n",
    "result = result.reset_index()\n",
    "# md_text = f\"近{monitor_hour}小时urule日志中评分的缺失情况为:\\n {result.to_string()}\"\n",
    "# print(md_text)\n",
    "print(f\"ios近{monitor_hour}小时urule日志中评分的缺失情况为:\")\n",
    "result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-03-29T02:53:13.469235Z",
     "start_time": "2024-03-29T02:53:13.439390Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": "      merchant_id                tx_id         create_time  \\\n60             AC  1223096197134708736 2024-03-29 10:27:41   \n75             AC  1223095794024345600 2024-03-29 10:26:14   \n76             AC  1223095794024345601 2024-03-29 10:26:13   \n77             AC  1223095794020151296 2024-03-29 10:26:13   \n78             AC  1223095798931681280 2024-03-29 10:26:12   \n...           ...                  ...                 ...   \n13472          AC  1222825632704524288 2024-03-28 16:32:33   \n13496          AC  1222823074028089344 2024-03-28 16:22:24   \n13730          AC  1222805807806574592 2024-03-28 15:13:47   \n13731          AC  1222805763393089536 2024-03-28 15:13:36   \n13733          AC  1222805698356211712 2024-03-28 15:13:21   \n\n                                                req_data  type_urule  type  \\\n60     {\"ADVANCE_GD_X_1\": -99, \"ADVANCE_GD_X_10\": -99...           2     2   \n75     {\"ADVANCE_GD_X_1\": -99, \"ADVANCE_GD_X_10\": -99...           1     1   \n76     {\"ADVANCE_GD_X_1\": -99, \"ADVANCE_GD_X_10\": -99...           1     1   \n77     {\"ADVANCE_GD_X_1\": -99, \"ADVANCE_GD_X_10\": -99...           1     1   \n78     {\"ADVANCE_GD_X_1\": -99, \"ADVANCE_GD_X_10\": -99...           1     1   \n...                                                  ...         ...   ...   \n13472  {\"ADVANCE_GD_X_1\": -99, \"ADVANCE_GD_X_10\": -99...           2     2   \n13496  {\"ADVANCE_GD_X_1\": -99, \"ADVANCE_GD_X_10\": -99...           2     2   \n13730  {\"ADVANCE_GD_X_1\": -99, \"ADVANCE_GD_X_10\": -99...           2     2   \n13731  {\"ADVANCE_GD_X_1\": -99, \"ADVANCE_GD_X_10\": -99...           2     2   \n13733  {\"ADVANCE_GD_X_1\": -99, \"ADVANCE_GD_X_10\": -99...           2     2   \n\n       status                                         apply_info      phone  \\\n60          0  {\"userBankInfo\":{\"appUserId\":\"1190786241203302...  930831872   \n75          1  {\"productInfo\":{\"amount\":96000.00,\"productCode...  964734807   \n76          1  {\"productInfo\":{\"amount\":85000.00,\"productCode...  964734807   \n77          1  {\"productInfo\":{\"amount\":88000.00,\"productCode...  964734807   \n78          1  {\"productInfo\":{\"amount\":110000.00,\"productCod...  964734807   \n...       ...                                                ...        ...   \n13472       0  {\"userBankInfo\":{\"appUserId\":\"1211516684215021...  920550355   \n13496       0  {\"userBankInfo\":{\"appUserId\":\"1191858575674732...  965357098   \n13730       0  {\"userBankInfo\":{\"appUserId\":\"1216616546065477...  975573450   \n13731       0  {\"userBankInfo\":{\"appUserId\":\"1216616546065477...  975573450   \n13733       0  {\"userBankInfo\":{\"appUserId\":\"1216616546065477...  975573450   \n\n                   user_id  ... scorev7  scorev8 scorev9  scorev10 scorev11  \\\n60     1190786241203302400  ...  -999.0   -999.0  -999.0    -999.0   -999.0   \n75     1195081520777818112  ...  -999.0   -999.0  -999.0    -999.0   -999.0   \n76     1195081520777818112  ...  -999.0   -999.0  -999.0    -999.0   -999.0   \n77     1195081520777818112  ...  -999.0   -999.0  -999.0    -999.0   -999.0   \n78     1195081520777818112  ...  -999.0   -999.0  -999.0    -999.0   -999.0   \n...                    ...  ...     ...      ...     ...       ...      ...   \n13472  1211516684215021568  ...  -999.0   -999.0  -999.0    -999.0   -999.0   \n13496  1191858575674732544  ...  -999.0   -999.0  -999.0    -999.0   -999.0   \n13730  1216616546065477632  ...  -999.0   -999.0  -999.0    -999.0    619.0   \n13731  1216616546065477632  ...  -999.0   -999.0  -999.0    -999.0    619.0   \n13733  1216616546065477632  ...  -999.0   -999.0  -999.0    -999.0    619.0   \n\n       scorev12  scorev13  clientType1  type1  endBillsCnt1  \n60       -999.0    -999.0      android     授信            新客  \n75       -999.0    -999.0      android     进件            老客  \n76       -999.0    -999.0      android     进件            老客  \n77       -999.0    -999.0      android     进件            老客  \n78       -999.0    -999.0      android     进件            老客  \n...         ...       ...          ...    ...           ...  \n13472    -999.0    -999.0      android     授信            新客  \n13496    -999.0    -999.0      android     授信            新客  \n13730     575.0    -999.0      android     授信            新客  \n13731     575.0    -999.0      android     授信            新客  \n13733     575.0    -999.0      android     授信            新客  \n\n[1536 rows x 31 columns]",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>merchant_id</th>\n      <th>tx_id</th>\n      <th>create_time</th>\n      <th>req_data</th>\n      <th>type_urule</th>\n      <th>type</th>\n      <th>status</th>\n      <th>apply_info</th>\n      <th>phone</th>\n      <th>user_id</th>\n      <th>...</th>\n      <th>scorev7</th>\n      <th>scorev8</th>\n      <th>scorev9</th>\n      <th>scorev10</th>\n      <th>scorev11</th>\n      <th>scorev12</th>\n      <th>scorev13</th>\n      <th>clientType1</th>\n      <th>type1</th>\n      <th>endBillsCnt1</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>60</th>\n      <td>AC</td>\n      <td>1223096197134708736</td>\n      <td>2024-03-29 10:27:41</td>\n      <td>{\"ADVANCE_GD_X_1\": -99, \"ADVANCE_GD_X_10\": -99...</td>\n      <td>2</td>\n      <td>2</td>\n      <td>0</td>\n      <td>{\"userBankInfo\":{\"appUserId\":\"1190786241203302...</td>\n      <td>930831872</td>\n      <td>1190786241203302400</td>\n      <td>...</td>\n      <td>-999.0</td>\n      <td>-999.0</td>\n      <td>-999.0</td>\n      <td>-999.0</td>\n      <td>-999.0</td>\n      <td>-999.0</td>\n      <td>-999.0</td>\n      <td>android</td>\n      <td>授信</td>\n      <td>新客</td>\n    </tr>\n    <tr>\n      <th>75</th>\n      <td>AC</td>\n      <td>1223095794024345600</td>\n      <td>2024-03-29 10:26:14</td>\n      <td>{\"ADVANCE_GD_X_1\": -99, \"ADVANCE_GD_X_10\": -99...</td>\n      <td>1</td>\n      <td>1</td>\n      <td>1</td>\n      <td>{\"productInfo\":{\"amount\":96000.00,\"productCode...</td>\n      <td>964734807</td>\n      <td>1195081520777818112</td>\n      <td>...</td>\n      <td>-999.0</td>\n      <td>-999.0</td>\n      <td>-999.0</td>\n      <td>-999.0</td>\n      <td>-999.0</td>\n      <td>-999.0</td>\n      <td>-999.0</td>\n      <td>android</td>\n      <td>进件</td>\n      <td>老客</td>\n    </tr>\n    <tr>\n      <th>76</th>\n      <td>AC</td>\n      <td>1223095794024345601</td>\n      <td>2024-03-29 10:26:13</td>\n      <td>{\"ADVANCE_GD_X_1\": -99, \"ADVANCE_GD_X_10\": -99...</td>\n      <td>1</td>\n      <td>1</td>\n      <td>1</td>\n      <td>{\"productInfo\":{\"amount\":85000.00,\"productCode...</td>\n      <td>964734807</td>\n      <td>1195081520777818112</td>\n      <td>...</td>\n      <td>-999.0</td>\n      <td>-999.0</td>\n      <td>-999.0</td>\n      <td>-999.0</td>\n      <td>-999.0</td>\n      <td>-999.0</td>\n      <td>-999.0</td>\n      <td>android</td>\n      <td>进件</td>\n      <td>老客</td>\n    </tr>\n    <tr>\n      <th>77</th>\n      <td>AC</td>\n      <td>1223095794020151296</td>\n      <td>2024-03-29 10:26:13</td>\n      <td>{\"ADVANCE_GD_X_1\": -99, \"ADVANCE_GD_X_10\": -99...</td>\n      <td>1</td>\n      <td>1</td>\n      <td>1</td>\n      <td>{\"productInfo\":{\"amount\":88000.00,\"productCode...</td>\n      <td>964734807</td>\n      <td>1195081520777818112</td>\n      <td>...</td>\n      <td>-999.0</td>\n      <td>-999.0</td>\n      <td>-999.0</td>\n      <td>-999.0</td>\n      <td>-999.0</td>\n      <td>-999.0</td>\n      <td>-999.0</td>\n      <td>android</td>\n      <td>进件</td>\n      <td>老客</td>\n    </tr>\n    <tr>\n      <th>78</th>\n      <td>AC</td>\n      <td>1223095798931681280</td>\n      <td>2024-03-29 10:26:12</td>\n      <td>{\"ADVANCE_GD_X_1\": -99, \"ADVANCE_GD_X_10\": -99...</td>\n      <td>1</td>\n      <td>1</td>\n      <td>1</td>\n      <td>{\"productInfo\":{\"amount\":110000.00,\"productCod...</td>\n      <td>964734807</td>\n      <td>1195081520777818112</td>\n      <td>...</td>\n      <td>-999.0</td>\n      <td>-999.0</td>\n      <td>-999.0</td>\n      <td>-999.0</td>\n      <td>-999.0</td>\n      <td>-999.0</td>\n      <td>-999.0</td>\n      <td>android</td>\n      <td>进件</td>\n      <td>老客</td>\n    </tr>\n    <tr>\n      <th>...</th>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th>13472</th>\n      <td>AC</td>\n      <td>1222825632704524288</td>\n      <td>2024-03-28 16:32:33</td>\n      <td>{\"ADVANCE_GD_X_1\": -99, \"ADVANCE_GD_X_10\": -99...</td>\n      <td>2</td>\n      <td>2</td>\n      <td>0</td>\n      <td>{\"userBankInfo\":{\"appUserId\":\"1211516684215021...</td>\n      <td>920550355</td>\n      <td>1211516684215021568</td>\n      <td>...</td>\n      <td>-999.0</td>\n      <td>-999.0</td>\n      <td>-999.0</td>\n      <td>-999.0</td>\n      <td>-999.0</td>\n      <td>-999.0</td>\n      <td>-999.0</td>\n      <td>android</td>\n      <td>授信</td>\n      <td>新客</td>\n    </tr>\n    <tr>\n      <th>13496</th>\n      <td>AC</td>\n      <td>1222823074028089344</td>\n      <td>2024-03-28 16:22:24</td>\n      <td>{\"ADVANCE_GD_X_1\": -99, \"ADVANCE_GD_X_10\": -99...</td>\n      <td>2</td>\n      <td>2</td>\n      <td>0</td>\n      <td>{\"userBankInfo\":{\"appUserId\":\"1191858575674732...</td>\n      <td>965357098</td>\n      <td>1191858575674732544</td>\n      <td>...</td>\n      <td>-999.0</td>\n      <td>-999.0</td>\n      <td>-999.0</td>\n      <td>-999.0</td>\n      <td>-999.0</td>\n      <td>-999.0</td>\n      <td>-999.0</td>\n      <td>android</td>\n      <td>授信</td>\n      <td>新客</td>\n    </tr>\n    <tr>\n      <th>13730</th>\n      <td>AC</td>\n      <td>1222805807806574592</td>\n      <td>2024-03-28 15:13:47</td>\n      <td>{\"ADVANCE_GD_X_1\": -99, \"ADVANCE_GD_X_10\": -99...</td>\n      <td>2</td>\n      <td>2</td>\n      <td>0</td>\n      <td>{\"userBankInfo\":{\"appUserId\":\"1216616546065477...</td>\n      <td>975573450</td>\n      <td>1216616546065477632</td>\n      <td>...</td>\n      <td>-999.0</td>\n      <td>-999.0</td>\n      <td>-999.0</td>\n      <td>-999.0</td>\n      <td>619.0</td>\n      <td>575.0</td>\n      <td>-999.0</td>\n      <td>android</td>\n      <td>授信</td>\n      <td>新客</td>\n    </tr>\n    <tr>\n      <th>13731</th>\n      <td>AC</td>\n      <td>1222805763393089536</td>\n      <td>2024-03-28 15:13:36</td>\n      <td>{\"ADVANCE_GD_X_1\": -99, \"ADVANCE_GD_X_10\": -99...</td>\n      <td>2</td>\n      <td>2</td>\n      <td>0</td>\n      <td>{\"userBankInfo\":{\"appUserId\":\"1216616546065477...</td>\n      <td>975573450</td>\n      <td>1216616546065477632</td>\n      <td>...</td>\n      <td>-999.0</td>\n      <td>-999.0</td>\n      <td>-999.0</td>\n      <td>-999.0</td>\n      <td>619.0</td>\n      <td>575.0</td>\n      <td>-999.0</td>\n      <td>android</td>\n      <td>授信</td>\n      <td>新客</td>\n    </tr>\n    <tr>\n      <th>13733</th>\n      <td>AC</td>\n      <td>1222805698356211712</td>\n      <td>2024-03-28 15:13:21</td>\n      <td>{\"ADVANCE_GD_X_1\": -99, \"ADVANCE_GD_X_10\": -99...</td>\n      <td>2</td>\n      <td>2</td>\n      <td>0</td>\n      <td>{\"userBankInfo\":{\"appUserId\":\"1216616546065477...</td>\n      <td>975573450</td>\n      <td>1216616546065477632</td>\n      <td>...</td>\n      <td>-999.0</td>\n      <td>-999.0</td>\n      <td>-999.0</td>\n      <td>-999.0</td>\n      <td>619.0</td>\n      <td>575.0</td>\n      <td>-999.0</td>\n      <td>android</td>\n      <td>授信</td>\n      <td>新客</td>\n    </tr>\n  </tbody>\n</table>\n<p>1536 rows × 31 columns</p>\n</div>"
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "condition = (urule_log_df['merchant_id']=='AC') & (urule_log_df['scorev2']<0)\n",
    "urule_log_df[condition]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "outputs": [],
   "source": [],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "outputs": [],
   "source": [],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "outputs": [],
   "source": [],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "outputs": [],
   "source": [],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "outputs": [],
   "source": [],
   "metadata": {
    "collapsed": false
   }
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "py311",
   "language": "python",
   "name": "py311"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
